﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using System.Collections.Generic;//Please add references
namespace Taxes.DAL
{
	/// <summary>
	/// 数据访问类:ysys_user
	/// </summary>
	public partial class ysys_user
	{
		public ysys_user()
		{}


        public List<Model.ysys_user> GetDepartment()
        {
            var sql = "select distinct  user_department from ysys_user";
            List<Model.ysys_user> users = new List<Model.ysys_user>();
            Random rnd = new Random();
            using (SqlDataReader sdr = DbHelperSQL.ExecuteReader(sql))
            {
                while (sdr.Read())
                {
                    var model =  new Model.ysys_user() { user_department = sdr["user_department"].ToString() };
                    model.ID = rnd.Next();
                    users.Add(model);
                }
            }
            //Model.ysys_user user = new Model.ysys_user();
            //user.user_department = "全部";
            //user.ID = 0;
            //users.Add(user);
            return users;
        }
        public List<Model.ysys_user> GetUsersByDepatment(string department)
        {
            var sql = "SELECT * FROM YSYS_USER WHERE user_department = @user_department";
            var parm = new SqlParameter[] {
                new SqlParameter("@user_department",department)
            };
            List<Model.ysys_user> users = new List<Model.ysys_user>();
            DataSet ds = DbHelperSQL.Query(sql, parm);
           
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                var model = new Model.ysys_user();
                if (row["ID"] != null && row["ID"].ToString() != "")
                {
                    model.ID = int.Parse(row["ID"].ToString());
                }
                if (row["user_name"] != null && row["user_name"].ToString() != "")
                {
                    model.user_name = row["user_name"].ToString();
                }
                if (row["user_pwd"] != null && row["user_pwd"].ToString() != "")
                {
                    model.user_pwd = row["user_pwd"].ToString();
                }
                if (row["user_tname"] != null && row["user_tname"].ToString() != "")
                {
                    model.user_tname = row["user_tname"].ToString();
                }
                if (row["user_department"] != null && row["user_department"].ToString() != "")
                {
                    model.user_department = row["user_department"].ToString();
                }
                if (row["user_type"] != null && row["user_type"].ToString() != "")
                {
                    model.user_type = int.Parse(row["user_type"].ToString());
                }
                if (row["user_py"] != null && row["user_py"].ToString() != "")
                {
                    model.user_py = row["user_py"].ToString();
                }
                if (row["user_rolename"] != null && row["user_rolename"].ToString() != "")
                {
                    model.user_rolename = row["user_rolename"].ToString();
                }
                if (row["user_operator"] != null && row["user_operator"].ToString() != "")
                {
                    model.user_operator = int.Parse(ds.Tables[0].Rows[0]["user_operator"].ToString());
                }
                if (row["in_date"] != null && row["in_date"].ToString() != "")
                {
                    model.in_date = DateTime.Parse(row["in_date"].ToString());
                }
                users.Add(model);
            }
            return users;
        }
        public Model.ysys_user Exist(string name, string pwd)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append("select * from ysys_user");
            strsql.Append(" where user_name = @Name and user_pwd = @Pwd");
            SqlParameter[] parameters = {
                                            new SqlParameter("@Name",DbType.String),
                                            new SqlParameter("@Pwd",DbType.String)
                                        };
            parameters[0].Value = name;
            parameters[1].Value = pwd;

            SqlDataReader sdr = DbHelperSQL.ExecuteReader(strsql.ToString(), parameters);
            
            if (sdr.Read())
            {
                Model.ysys_user userInfo = new Model.ysys_user();
                userInfo.ID = Convert.ToInt32(sdr["ID"]);
                userInfo.user_name = sdr["user_name"].ToString();
                userInfo.user_pwd = sdr["user_pwd"].ToString();
                userInfo.user_department = sdr["user_department"].ToString();
                userInfo.user_tname = sdr["user_tname"].ToString();
                userInfo.user_type = Convert.ToInt32(sdr["user_type"]);
                //后面字段没用暂时不取

                return userInfo;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 通过用户名修改密码
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool UpdatePwd(string name, string pwd)
        {
            StringBuilder strsql = new StringBuilder();
            strsql.Append("update ysys_user");
            strsql.Append(" set user_pwd = @Pwd where user_name = @Name");
            SqlParameter[] parameters = {
                                            new SqlParameter("@Pwd",DbType.String),
                                            new SqlParameter("@Name",DbType.String)                                            
                                        };
            parameters[0].Value = pwd;
            parameters[1].Value = name;

            return DbHelperSQL.ExecuteSql(strsql.ToString(), parameters) > 0;
        }

		#region  Method

		/// <summary>
		/// 得到最大ID
		/// </summary>
		public int GetMaxId()
		{
		return DbHelperSQL.GetMaxID("ID", "ysys_user"); 
		}

		/// <summary>
		/// 是否存在该记录
		/// </summary>
		public bool Exists(int ID)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select count(1) from ysys_user");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
};
			parameters[0].Value = ID;

			return DbHelperSQL.Exists(strSql.ToString(),parameters);
		}


		/// <summary>
		/// 增加一条数据
		/// </summary>
		public int Add(Taxes.Model.ysys_user model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("insert into ysys_user(");
			strSql.Append("user_name,user_pwd,user_tname,user_department,user_type,user_py,user_rolename,user_operator,in_date)");
			strSql.Append(" values (");
			strSql.Append("@user_name,@user_pwd,@user_tname,@user_department,@user_type,@user_py,@user_rolename,@user_operator,@in_date)");
			strSql.Append(";select @@IDENTITY");
			SqlParameter[] parameters = {
					new SqlParameter("@user_name", SqlDbType.VarChar,50),
					new SqlParameter("@user_pwd", SqlDbType.VarChar,50),
					new SqlParameter("@user_tname", SqlDbType.VarChar,50),
					new SqlParameter("@user_department", SqlDbType.VarChar,50),
					new SqlParameter("@user_type", SqlDbType.Int,4),
					new SqlParameter("@user_py", SqlDbType.VarChar,50),
					new SqlParameter("@user_rolename", SqlDbType.VarChar,50),
					new SqlParameter("@user_operator", SqlDbType.Int,4),
					new SqlParameter("@in_date", SqlDbType.DateTime)};
			parameters[0].Value = model.user_name;
			parameters[1].Value = model.user_pwd;
			parameters[2].Value = model.user_tname;
			parameters[3].Value = model.user_department;
			parameters[4].Value = model.user_type;
			parameters[5].Value = model.user_py;
			parameters[6].Value = model.user_rolename;
			parameters[7].Value = model.user_operator;
			parameters[8].Value = model.in_date;

			object obj = DbHelperSQL.GetSingle(strSql.ToString(),parameters);
			if (obj == null)
			{
				return 0;
			}
			else
			{
				return Convert.ToInt32(obj);
			}
		}
		/// <summary>
		/// 更新一条数据
		/// </summary>
		public bool Update(Taxes.Model.ysys_user model)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("update ysys_user set ");
			strSql.Append("user_name=@user_name,");
			strSql.Append("user_pwd=@user_pwd,");
			strSql.Append("user_tname=@user_tname,");
			strSql.Append("user_department=@user_department,");
			strSql.Append("user_type=@user_type,");
			strSql.Append("user_py=@user_py,");
			strSql.Append("user_rolename=@user_rolename,");
			strSql.Append("user_operator=@user_operator,");
			strSql.Append("in_date=@in_date");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@user_name", SqlDbType.VarChar,50),
					new SqlParameter("@user_pwd", SqlDbType.VarChar,50),
					new SqlParameter("@user_tname", SqlDbType.VarChar,50),
					new SqlParameter("@user_department", SqlDbType.VarChar,50),
					new SqlParameter("@user_type", SqlDbType.Int,4),
					new SqlParameter("@user_py", SqlDbType.VarChar,50),
					new SqlParameter("@user_rolename", SqlDbType.VarChar,50),
					new SqlParameter("@user_operator", SqlDbType.Int,4),
					new SqlParameter("@in_date", SqlDbType.DateTime),
					new SqlParameter("@ID", SqlDbType.Int,4)};
			parameters[0].Value = model.user_name;
			parameters[1].Value = model.user_pwd;
			parameters[2].Value = model.user_tname;
			parameters[3].Value = model.user_department;
			parameters[4].Value = model.user_type;
			parameters[5].Value = model.user_py;
			parameters[6].Value = model.user_rolename;
			parameters[7].Value = model.user_operator;
			parameters[8].Value = model.in_date;
			parameters[9].Value = model.ID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}

		/// <summary>
		/// 删除一条数据
		/// </summary>
		public bool Delete(int ID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from ysys_user ");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
};
			parameters[0].Value = ID;

			int rows=DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}
		/// <summary>
		/// 批量删除数据
		/// </summary>
		public bool DeleteList(string IDlist )
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("delete from ysys_user ");
			strSql.Append(" where ID in ("+IDlist + ")  ");
			int rows=DbHelperSQL.ExecuteSql(strSql.ToString());
			if (rows > 0)
			{
				return true;
			}
			else
			{
				return false;
			}
		}


		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public Taxes.Model.ysys_user GetModel(int ID)
		{
			
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select  top 1 ID,user_name,user_pwd,user_tname,user_department,user_type,user_py,user_rolename,user_operator,in_date from ysys_user ");
			strSql.Append(" where ID=@ID");
			SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
};
			parameters[0].Value = ID;

			Taxes.Model.ysys_user model=new Taxes.Model.ysys_user();
			DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
			if(ds.Tables[0].Rows.Count>0)
			{
				if(ds.Tables[0].Rows[0]["ID"]!=null && ds.Tables[0].Rows[0]["ID"].ToString()!="")
				{
					model.ID=int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
				}
				if(ds.Tables[0].Rows[0]["user_name"]!=null && ds.Tables[0].Rows[0]["user_name"].ToString()!="")
				{
					model.user_name=ds.Tables[0].Rows[0]["user_name"].ToString();
				}
				if(ds.Tables[0].Rows[0]["user_pwd"]!=null && ds.Tables[0].Rows[0]["user_pwd"].ToString()!="")
				{
					model.user_pwd=ds.Tables[0].Rows[0]["user_pwd"].ToString();
				}
				if(ds.Tables[0].Rows[0]["user_tname"]!=null && ds.Tables[0].Rows[0]["user_tname"].ToString()!="")
				{
					model.user_tname=ds.Tables[0].Rows[0]["user_tname"].ToString();
				}
				if(ds.Tables[0].Rows[0]["user_department"]!=null && ds.Tables[0].Rows[0]["user_department"].ToString()!="")
				{
					model.user_department=ds.Tables[0].Rows[0]["user_department"].ToString();
				}
				if(ds.Tables[0].Rows[0]["user_type"]!=null && ds.Tables[0].Rows[0]["user_type"].ToString()!="")
				{
					model.user_type=int.Parse(ds.Tables[0].Rows[0]["user_type"].ToString());
				}
				if(ds.Tables[0].Rows[0]["user_py"]!=null && ds.Tables[0].Rows[0]["user_py"].ToString()!="")
				{
					model.user_py=ds.Tables[0].Rows[0]["user_py"].ToString();
				}
				if(ds.Tables[0].Rows[0]["user_rolename"]!=null && ds.Tables[0].Rows[0]["user_rolename"].ToString()!="")
				{
					model.user_rolename=ds.Tables[0].Rows[0]["user_rolename"].ToString();
				}
				if(ds.Tables[0].Rows[0]["user_operator"]!=null && ds.Tables[0].Rows[0]["user_operator"].ToString()!="")
				{
					model.user_operator=int.Parse(ds.Tables[0].Rows[0]["user_operator"].ToString());
				}
				if(ds.Tables[0].Rows[0]["in_date"]!=null && ds.Tables[0].Rows[0]["in_date"].ToString()!="")
				{
					model.in_date=DateTime.Parse(ds.Tables[0].Rows[0]["in_date"].ToString());
				}
				return model;
			}
			else
			{
				return null;
			}
		}

		/// <summary>
		/// 获得数据列表
		/// </summary>
		public DataSet GetList(string strWhere)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ID,user_name,user_pwd,user_tname,user_department,user_type,user_py,user_rolename,user_operator,in_date ");
			strSql.Append(" FROM ysys_user ");
			if(!String.IsNullOrEmpty(strWhere))
			{
				strSql.Append(" where "+strWhere);
			}
            strSql.Append(" order by user_department,user_tname");
			return DbHelperSQL.Query(strSql.ToString());
		}

		/// <summary>
		/// 获得前几行数据
		/// </summary>
		public DataSet GetList(int Top,string strWhere,string filedOrder)
		{
			StringBuilder strSql=new StringBuilder();
			strSql.Append("select ");
			if(Top>0)
			{
				strSql.Append(" top "+Top.ToString());
			}
			strSql.Append(" ID,user_name,user_pwd,user_tname,user_department,user_type,user_py,user_rolename,user_operator,in_date ");
			strSql.Append(" FROM ysys_user ");
			if(strWhere.Trim()!="")
			{
				strSql.Append(" where "+strWhere);
			}
			strSql.Append(" order by " + filedOrder);
			return DbHelperSQL.Query(strSql.ToString());
		}

		/*
		/// <summary>
		/// 分页获取数据列表
		/// </summary>
		public DataSet GetList(int PageSize,int PageIndex,string strWhere)
		{
			SqlParameter[] parameters = {
					new SqlParameter("@tblName", SqlDbType.VarChar, 255),
					new SqlParameter("@fldName", SqlDbType.VarChar, 255),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@IsReCount", SqlDbType.Bit),
					new SqlParameter("@OrderType", SqlDbType.Bit),
					new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
					};
			parameters[0].Value = "ysys_user";
			parameters[1].Value = "ID";
			parameters[2].Value = PageSize;
			parameters[3].Value = PageIndex;
			parameters[4].Value = 0;
			parameters[5].Value = 0;
			parameters[6].Value = strWhere;	
			return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
		}*/

		#endregion  Method
	}
}

